<?php
/**
 * Class that operate on table 'doctors'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2015-05-26 14:36
 */
class DoctorsMySqlDAO implements DoctorsDAO{

	/**
	 * Get Domain object by primry key
	 *
	 * @param String $id primary key
	 * @return DoctorsMySql 
	 */
	public function load($doctor){
		$sql = 'SELECT d.*,dc.category_id,dc.category_name,st.state_id,st.state_name FROM doctors d left join doctors_category dc on d.category_id=dc.category_id left join states st on d.state_id=st.state_id WHERE doctor_id = "'.$doctor->doctor_id.'"';
		$sqlQuery = new SqlQuery($sql);
		return $this->getRow($sqlQuery);
	}

	public function doctorstates_list($doctorsCategory){
		$sql = 'SELECT state_id,state_name FROM states';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	/**
	 * Get all records from table
	 */
	
	/**
	 * Get all records from table ordered by field
	 *
	 * @param $orderColumn column name
	 */
	public function queryAllSearchBy($doctor){
		$sql = 'SELECT d.*,dc.category_name,st.state_id,st.state_name FROM doctors d left join doctors_category dc on d.category_id=dc.category_id left join states st on d.state_id=st.state_id WHERE '.$doctor->search_by.' like"%'.$doctor->search_by_value.'%" order by '.$doctor->order_by.' '.$doctor->sort_order.' limit '.$doctor->results_per_page.' offset '.$doctor->offset.'';
		//print_r($sql);exit;
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function SearchByCount($doctor){
		$sql = 'SELECT count(*) FROM doctors WHERE '.$doctor->search_by.' like"%'.$doctor->search_by_value.'%" order by '.$doctor->order_by.' '.$doctor->sort_order.' limit '.$doctor->results_per_page.' offset '.$doctor->offset.'';
		//print_r($sql);exit;
		$sqlQuery = new SqlQuery($sql);
		return $this->querySingleResult($sqlQuery);
	}
	
	public function queryAll($doctor){
		$sql = 'SELECT d.*,dc.category_name,st.state_id,st.state_name FROM doctors d left join doctors_category dc on d.category_id=dc.category_id left join states st on d.state_id=st.state_id order by '.$doctor->order_by.' '.$doctor->sort_order.' limit '.$doctor->results_per_page.' offset '.$doctor->offset.'';
		//print_r($sql);exit;
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	public function queryAllCount($doctor){
		$sql = 'SELECT count(*) FROM doctors order by '.$doctor->order_by.' '.$doctor->sort_order.' limit '.$doctor->results_per_page.' offset '.$doctor->offset.'';
		//print_r($sql);exit;
		$sqlQuery = new SqlQuery($sql);
		return $this->querySingleResult($sqlQuery);
	}
	
	/**
 	 * Delete record from table
 	 * @param doctor primary key
 	 */
	public function delete($doctor){
		$sql = 'UPDATE doctors SET status = "'.$doctor->status.'" WHERE doctor_id = "'.$doctor->doctor_id.'"';
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}
	
	/**
 	 * Insert record to table
 	 *
 	 * @param DoctorsMySql doctor
 	 */
	public function insert($doctor){
		$sql = 'INSERT INTO doctors (doctor_name, category_id, doctor_address, doctor_city, state_id, doctor_zipcode, doctor_comments, created_date, updated_date) VALUES ("'.$doctor->doctor_name.'", "'.$doctor->category_id.'", "'.$doctor->doctor_address.'", "'.$doctor->doctor_city.'", "'.$doctor->state_id.'", "'.$doctor->doctor_zipcode.'", "'.$doctor->doctor_comments.'", "'.$doctor->created_date.'", "'.$doctor->updated_date.'")';
		$sqlQuery = new SqlQuery($sql);
		$id = $this->executeInsert($sqlQuery);	
		$doctor->doctor_id = $id;
		return $id;
	}
	
	/**
 	 * Update record in table
 	 *
 	 * @param DoctorsMySql doctor
 	 */
	public function update($doctor){
		$sql = 'UPDATE doctors SET doctor_name = ?, category_id = ?, doctor_address = ?, doctor_city = ?, state_id = ?, doctor_zipcode = ?, doctor_comments = ?, updated_date = ?, status = ? WHERE doctor_id = ?';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->set($doctor->doctor_name);
		$sqlQuery->setNumber($doctor->category_id);
		$sqlQuery->set($doctor->doctor_address);
		$sqlQuery->set($doctor->doctor_city);
		$sqlQuery->set($doctor->state_id);
		$sqlQuery->set($doctor->doctor_zipcode);
		$sqlQuery->set($doctor->doctor_comments);
		$sqlQuery->set($doctor->updated_date);
		$sqlQuery->setNumber($doctor->status);
		$sqlQuery->setNumber($doctor->doctor_id);
		return $this->executeUpdate($sqlQuery);
	}

	/**
 	 * Delete all rows
 	 */
	public function clean(){
		$sql = 'DELETE FROM doctors';
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}

	public function queryByDoctorName($value){
		$sql = 'SELECT * FROM doctors WHERE doctor_name = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByCategoryId($value){
		$sql = 'SELECT * FROM doctors WHERE category_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}

	public function queryByDoctorAddress($value){
		$sql = 'SELECT * FROM doctors WHERE doctor_address = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByDoctorCity($value){
		$sql = 'SELECT * FROM doctors WHERE doctor_city = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByStateId($value){
		$sql = 'SELECT * FROM doctors WHERE state_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByDoctorZipcode($value){
		$sql = 'SELECT * FROM doctors WHERE doctor_zipcode = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByDoctorComments($value){
		$sql = 'SELECT * FROM doctors WHERE doctor_comments = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByCreatedDate($value){
		$sql = 'SELECT * FROM doctors WHERE created_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByUpdatedDate($value){
		$sql = 'SELECT * FROM doctors WHERE updated_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByStatus($value){
		$sql = 'SELECT * FROM doctors WHERE status = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}


	public function deleteByDoctorName($value){
		$sql = 'DELETE FROM doctors WHERE doctor_name = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByCategoryId($value){
		$sql = 'DELETE FROM doctors WHERE category_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByDoctorAddress($value){
		$sql = 'DELETE FROM doctors WHERE doctor_address = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByDoctorCity($value){
		$sql = 'DELETE FROM doctors WHERE doctor_city = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByStateId($value){
		$sql = 'DELETE FROM doctors WHERE state_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByDoctorZipcode($value){
		$sql = 'DELETE FROM doctors WHERE doctor_zipcode = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByDoctorComments($value){
		$sql = 'DELETE FROM doctors WHERE doctor_comments = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByCreatedDate($value){
		$sql = 'DELETE FROM doctors WHERE created_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByUpdatedDate($value){
		$sql = 'DELETE FROM doctors WHERE updated_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByStatus($value){
		$sql = 'DELETE FROM doctors WHERE status = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}


	
	/**
	 * Read row
	 *
	 * @return DoctorsMySql 
	 */
	protected function readRow($row){
		$doctor = new Doctor();
		
		$doctor->doctor_id = $row['doctor_id'];
		$doctor->doctor_name = $row['doctor_name'];
		$doctor->category_id = $row['category_id'];
		$doctor->category_name = $row['category_name'];
		$doctor->state_name = $row['state_name'];
		$doctor->doctor_address = $row['doctor_address'];
		$doctor->doctor_city = $row['doctor_city'];
		$doctor->state_id = $row['state_id'];
		$doctor->doctor_zipcode = $row['doctor_zipcode'];
		$doctor->doctor_comments = $row['doctor_comments'];
		$doctor->created_date = $row['created_date'];
		$doctor->updated_date = $row['updated_date'];
		$doctor->status = $row['status'];

		return $doctor;
	}
	
	protected function getList($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		$ret = array();
		for($i=0;$i<count($tab);$i++){
			$ret[$i] = $this->readRow($tab[$i]);
		}
		return $ret;
	}
	
	/**
	 * Get row
	 *
	 * @return DoctorsMySql 
	 */
	protected function getRow($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		if(count($tab)==0){
			return null;
		}
		return $this->readRow($tab[0]);		
	}
	
	/**
	 * Execute sql query
	 */
	protected function execute($sqlQuery){
		return QueryExecutor::execute($sqlQuery);
	}
	
		
	/**
	 * Execute sql query
	 */
	protected function executeUpdate($sqlQuery){
		return QueryExecutor::executeUpdate($sqlQuery);
	}

	/**
	 * Query for one row and one column
	 */
	protected function querySingleResult($sqlQuery){
		return QueryExecutor::queryForString($sqlQuery);
	}

	/**
	 * Insert row to table
	 */
	protected function executeInsert($sqlQuery){
		return QueryExecutor::executeInsert($sqlQuery);
	}
}
?>